package buildcode.util;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;
import java.util.Properties;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang3.ArrayUtils;

import buildcode.database.DataBaseTable;
import buildcode.database.EnumColumnInfo;
import buildcode.database.TableColumnInfo;
import buildcode.database.convert.NameConvert;
import buildcode.database.convert.UnderLineToCamelCaseConvert;

public class DataBaseUtil {
    private static NameConvert namceConvert = new UnderLineToCamelCaseConvert();

    private static final Map<Integer, String> JDBC_INT_NAME_MAP = new HashMap<Integer, String>();

    private static Pattern enumPattern = Pattern.compile("\\d+");

    private static String PRIMARY_INDEX = "PRIVATE_INDEX";
    private static String UNIQUE_INDEX = "UNIQUE_INDEX";

    static {
        JDBC_INT_NAME_MAP.put(Types.BIT, "BIT");
        JDBC_INT_NAME_MAP.put(Types.TINYINT, "TINYINT");
        JDBC_INT_NAME_MAP.put(Types.SMALLINT, "SMALLINT");
        JDBC_INT_NAME_MAP.put(Types.INTEGER, "INTEGER");
        JDBC_INT_NAME_MAP.put(Types.BIGINT, "BIGINT");
        JDBC_INT_NAME_MAP.put(Types.FLOAT, "FLOAT");
        JDBC_INT_NAME_MAP.put(Types.REAL, "REAL");
        JDBC_INT_NAME_MAP.put(Types.DOUBLE, "DOUBLE");
        JDBC_INT_NAME_MAP.put(Types.NUMERIC, "NUMERIC");
        JDBC_INT_NAME_MAP.put(Types.DECIMAL, "DECIMAL");
        JDBC_INT_NAME_MAP.put(Types.CHAR, "CHAR");
        JDBC_INT_NAME_MAP.put(Types.VARCHAR, "VARCHAR");
        JDBC_INT_NAME_MAP.put(Types.LONGVARCHAR, "LONGVARCHAR");
        JDBC_INT_NAME_MAP.put(Types.DATE, "DATE");
        JDBC_INT_NAME_MAP.put(Types.TIME, "TIME");
        JDBC_INT_NAME_MAP.put(Types.TIMESTAMP, "TIMESTAMP");
        JDBC_INT_NAME_MAP.put(Types.BINARY, "BINARY");
        JDBC_INT_NAME_MAP.put(Types.VARBINARY, "VARBINARY");
        JDBC_INT_NAME_MAP.put(Types.LONGVARBINARY, "LONGVARBINARY");
        JDBC_INT_NAME_MAP.put(Types.NULL, "NULL");
        JDBC_INT_NAME_MAP.put(Types.OTHER, "OTHER");
        JDBC_INT_NAME_MAP.put(Types.JAVA_OBJECT, "JAVA_OBJECT");
        JDBC_INT_NAME_MAP.put(Types.DISTINCT, "DISTINCT");
        JDBC_INT_NAME_MAP.put(Types.STRUCT, "STRUCT");
        JDBC_INT_NAME_MAP.put(Types.ARRAY, "ARRAY");
        JDBC_INT_NAME_MAP.put(Types.BLOB, "BLOB");
        JDBC_INT_NAME_MAP.put(Types.CLOB, "CLOB");
        JDBC_INT_NAME_MAP.put(Types.REF, "REF");
        JDBC_INT_NAME_MAP.put(Types.DATALINK, "DATALINK");
        JDBC_INT_NAME_MAP.put(Types.BOOLEAN, "BOOLEAN");
        JDBC_INT_NAME_MAP.put(Types.ROWID, "ROWID");
        JDBC_INT_NAME_MAP.put(Types.NCHAR, "NCHAR");
        JDBC_INT_NAME_MAP.put(Types.NVARCHAR, "NVARCHAR");
        JDBC_INT_NAME_MAP.put(Types.LONGNVARCHAR, "LONGNVARCHAR");
        JDBC_INT_NAME_MAP.put(Types.SQLXML, "SQLXML");
        JDBC_INT_NAME_MAP.put(Types.REF_CURSOR, "REF_CURSOR");
        JDBC_INT_NAME_MAP.put(Types.TIME_WITH_TIMEZONE, "TIME_WITH_TIMEZONE");
        JDBC_INT_NAME_MAP.put(Types.TIMESTAMP_WITH_TIMEZONE, "TIMESTAMP_WITH_TIMEZONE");

    }

    public static List<DataBaseTable> getTableInfo(String url, String userName, String password, String... tableNames) {
        List<DataBaseTable> list = new ArrayList<DataBaseTable>();
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
        Properties properties = new Properties();
        properties.setProperty("user", userName);
        properties.setProperty("password", password);
        properties.setProperty("remarks", "true");
        properties.setProperty("useInformationSchema", "true");
        try (Connection connection = DriverManager.getConnection(url, properties)) {
            // 创建 Statement 对象
            try (Statement stmt = connection.createStatement()) {
                // 获取数据库元数据
                DatabaseMetaData metadata = connection.getMetaData();
                // 获取所有表名
                ResultSet tables = metadata.getTables(null, null, "%", new String[] { "TABLE" });

                while (tables.next()) {
                    String tableName = tables.getString("TABLE_NAME");
                    if (ArrayUtils.contains(tableNames, tableName)) {
                        DataBaseTable table = new DataBaseTable(tableName, CommonUtil.firstUpper(namceConvert.convert(tableName)), tables.getString("REMARKS"));
                        ResultSet columns = metadata.getColumns(null, null, tableName, null);
                        Map<String, String> indexInfo = getIndexInfo(metadata, tableName);
                        while (columns.next()) {
                            String columnName = columns.getString("COLUMN_NAME");
                            int dataType = columns.getInt("DATA_TYPE");
                            TableColumnInfo info = new TableColumnInfo();
                            info.setColumnName(columnName);
                            info.setJdbcTypeName(JDBC_INT_NAME_MAP.get(dataType));
                            info.setCamelCaseName(getJavaPropertyName(columnName));
                            info.setJdbcType(dataType);
                            info.setNote(columns.getString("REMARKS"));
                            if (dataType == Types.TINYINT) {
                                info.setEnumColumnInfo(getEnumColumnByRemark(info.getNote()));
                            }
                            int isNullable = columns.getInt("NULLABLE");
                            info.setNull(DatabaseMetaData.columnNullable == isNullable);
                            int columnSize = columns.getInt("COLUMN_SIZE");
                            info.setStrlength(columnSize);
                            info.setPrimaryIndex(Optional.ofNullable(indexInfo.get(columnName)).map(v -> v.equals(PRIMARY_INDEX)).orElse(false));
                            info.setUniqueIndex(Optional.ofNullable(indexInfo.get(columnName)).map(v -> v.equals(UNIQUE_INDEX)).orElse(false));
                            if (info.isPrimaryIndex()) {
                                table.setPrimaryColumn(info);
                            }
                            table.getColumns().add(info);
                        }
                        if (table.getPrimaryColumn() == null) {
                            table.setPrimaryColumn(table.getColumns().get(0));
                        }
                        list.add(table);
                    }

                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public static String getJavaPropertyName(String columnName) {
        return namceConvert.convert(columnName);
    }

    public static boolean isNeedImport(String desPackage, String contextPackage) {
        if (desPackage.contains("java.lang") || desPackage.equals(contextPackage)) {
            return false;
        }
        return true;
    }

    public static EnumColumnInfo getEnumColumnByRemark(String value) {
        Matcher matcher = enumPattern.matcher(value);
        List<Integer> start = new ArrayList<Integer>();
        List<Integer> end = new ArrayList<Integer>();
        List<Integer> kes = new ArrayList<Integer>();
        while (matcher.find()) {
            start.add(matcher.end());
            end.add(matcher.start());
            kes.add(Integer.valueOf(matcher.group()));
        }
        if (!end.isEmpty()) {
            Map<Integer, String> res = new LinkedHashMap<Integer, String>();
            int columnDescEndIndex = end.get(0);
            end = end.subList(1, end.size());
            end.add(value.length());
            for (int i = 0; i < start.size(); i++) {
                String val = value.substring(start.get(i), end.get(i));
                val = val.replaceAll("[\\s*\\-:：，,（）\\(\\)]", "");
                Integer key = kes.get(i);
                res.putIfAbsent(key, val);
            }
            return new EnumColumnInfo(value.substring(0, columnDescEndIndex), res, value);
        }
        return null;
    }

    private static Map<String, String> getIndexInfo(DatabaseMetaData metadata, String tableName) throws SQLException {
        Map<String, String> res = new HashMap<String, String>();

        ResultSet indexes = metadata.getIndexInfo(null, null, tableName, true, false);

        while (indexes.next()) {
            boolean nonUnique = indexes.getBoolean("NON_UNIQUE");
            String indexColumnName = indexes.getString("COLUMN_NAME");
            // 如果不是唯一索引，跳过
            if (nonUnique) {
                continue;
            }
            // 如果是唯一索引，记录列名
            res.put(indexColumnName, UNIQUE_INDEX);
        }

        ResultSet primaryKeys = metadata.getPrimaryKeys(null, null, tableName);
        while (primaryKeys.next()) {
            String pkColumnName = primaryKeys.getString("COLUMN_NAME");
            res.put(pkColumnName, PRIMARY_INDEX);
        }
        return res;
    }

}
